﻿/******************************************************************************
'Description:分厂重点件号排产进度类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2018.06.21
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class uMESJgjFactoryProcessLookBoardBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();
        private uMESCommonMethod common = new uMESCommonMethod();

        public DataTable GetFactoryInfo(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM factory f WHERE f.factoryid ='{0}'", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        public DataTable GetWorkcentername(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT w.workcentername,w.workcenterid");
            sb.AppendLine("FROM workcenter w");
            sb.AppendFormat("WHERE w.wctype IS NULL AND w.factoryid ='{0}' AND w.notes is null ", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 模糊匹配查询件号 add zhunan 2018.10.23
        public DataTable dtGetProductname(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT pb.productname");
            sb.AppendLine("FROM productbase pb");
            sb.AppendFormat("WHERE pb.productname LIKE '%{0}%'",para["productname"].ToString().ToUpper());
            sb.AppendLine(" AND ROWNUM < 11");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //工序排产完成情况看板（单件单卡不合并）
        public DataTable GetJgjFocusContainerProcess(Dictionary<string, object> para, out int pageCount, out int containerCount, out int rowCount)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT cci.containername");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
            sb.AppendLine("WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL AND cci.workflowroute IS NULL ");
            if (para.ContainsKey("factoryname"))
            {
                sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"].ToString());
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND cci.fromworkcentername = '{0}'", para["workcentername"].ToString());
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                }
            }
            if (para.ContainsKey("isFocusProductname"))
            {
                
                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sb.AppendLine(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sb.AppendLine(" AND fp.productname IS NULL ");
                }
            }
            if (para.ContainsKey("containername"))
            {
                if (para["containername"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                }
            }
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sb.ToString(), Convert.ToInt32(para["currentPage"]), Convert.ToInt32(para["pageSize"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            containerCount = 0;

            DataTable dtMain = retR.DBTable;

            if (dtMain.Rows.Count == 0)
            {
                return new DataTable();
            }

            StringBuilder sbContainerNameList = new StringBuilder();
            sbContainerNameList.Append("(");

            foreach (DataRow dr in dtMain.Rows)
            {
                sbContainerNameList.AppendFormat("'{0}',", dr["containername"].ToString());
            }
            sbContainerNameList.Remove(sbContainerNameList.Length - 1, 1);
            sbContainerNameList.Append(")");

            sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT cci.plannedcompletiondate,cci.fromworkcentername,cci.containername,ws.workflowstepname,ws.sequence,");
            sb.AppendLine("       cci.workflowstepsequence,CASE WHEN csd.plannedfinishdate IS NOT NULL THEN to_date(to_char(csd.plannedfinishdate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ELSE csd.plannedfinishdate END plannedfinishdate,");
            sb.AppendLine("       NVL(e2.fullname,e.fullname) fullname,csr.checkdate,s.specid,CASE WHEN fp.productname IS NOT NULL THEN '重点件号' ELSE '非重点件号' END producttype");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");//批次当前工艺路线上所有工序
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containerstepdispatch cst ON cst.containerid = cci.containerid AND cst.workflowstepid = ws.workflowstepid");
            sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid --AND csd.plantype = 1 ");
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = cst.workerid");
            sb.AppendLine("LEFT JOIN containerstepdispatchdetail cspd ON cspd.containerstepdispatchid = cst.containerstepdispatchid");
            sb.AppendLine("LEFT JOIN employee e2 ON e2.employeeid = cspd.workerid");
            sb.AppendLine("LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE cci.containername IN {0}", sbContainerNameList.ToString());
            //sb.AppendLine(" AND csd.plannedfinishdate IS NOT NULL AND csd.plantype = 1 ");
            sb.AppendLine("ORDER BY cci.containername,ws.sequence");

            DataTable dtTemp = dal.GetDataTable(sb.ToString());

            DataTable dtResult = dtTemp.DefaultView.ToTable(true, "plannedcompletiondate", "fromworkcentername", "containername", "workflowstepname", "sequence", "workflowstepsequence", "plannedfinishdate", "specid","producttype");
            
            dtResult.Columns.Add("fullname");
            dtResult.Columns.Add("checkdate");

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("containername = '{0}' and workflowstepname = '{1}'",dr["containername"],dr["workflowstepname"]);

                DataRow[] drlist = dtTemp.Select(filter);
                StringBuilder fullname = new StringBuilder();

                foreach (DataRow row in drlist)
                {
                    if (!fullname.ToString().Contains(row["fullname"].ToString()))
                    {
                        fullname.Append(row["fullname"].ToString()+"、");
                    }
                }
                if (fullname.ToString().Contains("、"))
                {
                    fullname.Remove(fullname.Length-1, 1);  
                }
                dr["fullname"] = fullname.ToString();
                dr["checkdate"] = dtTemp.Compute("Max(checkdate)", filter);
            }


            //查询自动转工工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE s.factoryid <> cci.originalfactoryid AND cci.containername IN {0}", sbContainerNameList.ToString());

            DataTable dtAutoSynergicSpecInfo = dal.GetDataTable(sb.ToString());

            //查询固定外委工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,cm.indate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE s.controlcode = 'LY05' AND cci.containername IN {0}", sbContainerNameList.ToString());

            DataTable dtAutoCommissioning = dal.GetDataTable(sb.ToString());


            //查询手动转工工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containershiprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE csr.containershiprecordid IS NOT NULL AND s.factoryid = cci.originalfactoryid AND cci.containername IN {0}", sbContainerNameList.ToString());

            DataTable dtSynergicInfo = dal.GetDataTable(sb.ToString());


            //自动转工序、临时转工序、固定外委序完工时间处理
            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                string filter = string.Format("containername = '{0}' and workflowstepname ='{1}'", dtResult.Rows[i]["containername"], dtResult.Rows[i]["workflowstepname"]);
                DataRow[] drAutoSynergic = dtAutoSynergicSpecInfo.Select(filter);
                DataRow[] drAutoCommissioning = dtAutoCommissioning.Select(filter);
                DataRow[] drSynergicInfo = dtSynergicInfo.Select(filter);
                //如果工序是外委序,将外委调度时间置为该工序检验时间
                if (drAutoSynergic.Length > 0)
                {
                    dtResult.Rows[i]["checkdate"] = drAutoSynergic[0]["recivedate"];
                }
                //如果工序为自动转工序,将转入时间置为该工序检验时间
                if (drAutoCommissioning.Length > 0)
                {
                    dtResult.Rows[i]["checkdate"] = drAutoCommissioning[0]["indate"];
                }
                //如果工序为临时转工序,将转入时间置为该工序检验时间
                if (drSynergicInfo.Length > 0)
                {
                    dtResult.Rows[i]["checkdate"] = drSynergicInfo[0]["recivedate"];
                }

            }

            dtResult.Columns.Add("status", typeof(string));//状态

            //计算批次各工序当前状态
            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                //要求完成时间、检验时间都不为空
                if ((dtResult.Rows[i]["checkdate"].ToString() != "") && (dtResult.Rows[i]["plannedfinishdate"].ToString() != ""))
                {
                    DateTime checkdate = Convert.ToDateTime(dtResult.Rows[i]["checkdate"]);
                    DateTime plannedfinishdate = Convert.ToDateTime(dtResult.Rows[i]["plannedfinishdate"]);
                    //检验时间 > 要求完成时间，标记状态为"超期完成"
                    if (DateTime.Compare(checkdate,plannedfinishdate) > 0)
                    {
                        dtResult.Rows[i]["status"] = "超期完成";
                    }
                    else
                    {
                        //检验时间 <= 要求完成时间，标记状态为"按时完成"
                        dtResult.Rows[i]["status"] = "按时完成";
                    }
                }
                //要求完成时间不为空，检验时间为空
                if ((dtResult.Rows[i]["checkdate"].ToString() == "") && (dtResult.Rows[i]["plannedfinishdate"].ToString() != ""))
                {
                    DateTime now = DateTime.Now;
                    DateTime plannedfinishdate = Convert.ToDateTime(dtResult.Rows[i]["plannedfinishdate"]);
                    //如果当前时间>要求完成时间，标记状态为"超期未完成"
                    if (DateTime.Compare(now,plannedfinishdate) >= 0)
                    {
                        dtResult.Rows[i]["status"] = "超期未完成";
                    }
                    else
                    {
                        //如果当前时间 <= 要求完成时间，标记状态为"未检验"
                        dtResult.Rows[i]["status"] = "未检验";
                    }
                }
                //要求完成时间为空,则工序没有做排产操作，此时标记状态'工序为派工'
                if (dtResult.Rows[i]["plannedfinishdate"].ToString() == "")
                {
                    dtResult.Rows[i]["status"] = "工序未排产";
                }
            }


            DataTable dtContainer = dtResult.DefaultView.ToTable(true, "plannedcompletiondate", "fromworkcentername", "containername", "workflowstepsequence");
            dtContainer.Columns.Add("maxSequence", typeof(Int32)); // 最大工序
            dtContainer.Columns.Add("startSequence", typeof(Int32));// 起始序
            dtContainer.Columns.Add("endSequence", typeof(Int32));// 结束工序

            // 计算批次的工序总数,计算每个批次显示的起始工序号,结束工序号
            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" containername = '{0}'", dr["containername"]);
                int maxSequence = Convert.ToInt32(dtResult.Compute("Max(sequence)", filter).ToString());
                dr["maxSequence"] = Convert.ToInt32(maxSequence);
                int currentSeq = Convert.ToInt32(dr["workflowstepsequence"].ToString());
                int startSeq = 0, endSeq = 0;

                // 批次工序总数小于7序,显示所有工序
                if (maxSequence <= 7)
                {
                    startSeq = 1;
                    endSeq = 7;
                    dr["startSequence"] = startSeq;
                    dr["endSequence"] = endSeq;
                }
                else
                {
                    // 批次工序总数大于7,且当前序不大于第4序时,显示1--7序
                    if (currentSeq <= 3)
                    {
                        startSeq = 1;
                        endSeq = 7;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) <= maxSequence))
                    {
                        startSeq = currentSeq - 2;
                        endSeq = 4 + currentSeq;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) > maxSequence))
                    {
                        startSeq = maxSequence - 6;
                        endSeq = maxSequence;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                }

            }

            DataTable dtFocusProcess = dtResult.Clone();

            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" containername = '{0}' and sequence >= {1} and sequence <= {2}",
                                                dr["containername"], dr["startSequence"], dr["endSequence"]);

                DataRow[] drList = dtResult.Select(filter);
                for (int i = 0; i < drList.Length; i++)
                {
                    DataRow datarow = dtFocusProcess.NewRow();
                    datarow["plannedcompletiondate"] = drList[i]["plannedcompletiondate"];
                    datarow["fromworkcentername"] = drList[i]["fromworkcentername"];
                    datarow["containername"] = drList[i]["containername"];
                    datarow["workflowstepname"] = drList[i]["workflowstepname"];
                    datarow["sequence"] = drList[i]["sequence"];
                    datarow["workflowstepsequence"] = drList[i]["workflowstepsequence"];
                    datarow["plannedfinishdate"] = drList[i]["plannedfinishdate"];
                    datarow["fullname"] = drList[i]["fullname"];
                    datarow["checkdate"] = drList[i]["checkdate"];
                    datarow["status"] = drList[i]["status"];
                    datarow["producttype"] = drList[i]["producttype"];
                    dtFocusProcess.Rows.Add(datarow);
                }

                // 每个批次显示7道序，当工序数不够7时，添加空行凑够7道序
                if (drList.Length < 7)
                {
                    for (int i = 0; i < (7 - drList.Length); i++)
                    {
                        DataRow datarow = dtFocusProcess.NewRow();
                        datarow["fromworkcentername"] = dr["fromworkcentername"];
                        datarow["containername"] = dr["containername"];
                        dtFocusProcess.Rows.Add(datarow);
                    }
                }
            }

            foreach (DataRow dr in dtFocusProcess.Rows)
            {
                //dr["workflowstepname"] = common.GetSpecNameWithOutProdName(dr["workflowstepname"].ToString());
                // 工序名称太长时，对超出长度的部分显示 "..."
                if (dr["workflowstepname"].ToString().Length > 13)
                {
                    dr["workflowstepname"] = dr["workflowstepname"].ToString().Substring(0, 10) + "...";
                }
                if ((dr["workflowstepsequence"].ToString() == dr["sequence"].ToString()) && dr["workflowstepsequence"].ToString() != "")
                {
                    dr["workflowstepname"] = "<i class='layui-icon layui-icon-flag'></i> " + dr["workflowstepname"];
                }
            }

            containerCount = dtFocusProcess.Rows.Count / 7;

            return dtFocusProcess;
        }

        //工序排产完成情况看板（旧方法） add zhunan 2018.12.04
        public DataTable GetContainerStepPlanFinishInfo(Dictionary<string, object> para, out int pageCount, out int containerCount, out int rowCount)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.*");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("      SELECT cci.togetherflag,cci.workflowstepname,cci.workflowstepid");
            sb.AppendLine("      FROM containercurrentinfo cci");
            sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
            sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
            sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype = 7 ");
            #region 查询条件
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryname = '{0}'", para["factoryname"].ToString());
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND cci.fromworkcentername = '{0}'", para["workcentername"].ToString());
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                }
            }
            if (para.ContainsKey("isFocusProductname"))
            {

                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sb.AppendLine(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sb.AppendLine(" AND fp.productname IS NULL ");
                }
            }
            #endregion
            sb.AppendLine("      GROUP BY cci.togetherflag,cci.workflowstepname,cci.workflowstepid");
            sb.AppendLine("");
            sb.AppendLine("      UNION ALL");
            sb.AppendLine("");
            sb.AppendLine("      SELECT cci.togetherflag,cci.workflowstepname,cci.workflowstepid");
            sb.AppendLine("      FROM containercurrentinfo cci");
            sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
            sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
            sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype <> 7 ");
            #region 查询条件
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryname = '{0}'", para["factoryname"].ToString());
                }
            }
            
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND cci.fromworkcentername = '{0}'", para["workcentername"].ToString());
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                }
            }
            if (para.ContainsKey("isFocusProductname"))
            {

                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sb.AppendLine(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sb.AppendLine(" AND fp.productname IS NULL ");
                }
            }
            #endregion
            sb.AppendLine("      GROUP BY cci.togetherflag,cci.workflowstepname,cci.workflowstepid");
            sb.AppendLine(") t");
            sb.AppendLine("ORDER BY t.togetherflag");

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sb.ToString(), Convert.ToInt32(para["currentPage"]), Convert.ToInt32(para["pageSize"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            containerCount = 0;

            if (retR.DBTable.Rows.Count == 0)
            {
                return new DataTable();
            }

            DataTable dtMain = retR.DBTable;

            //sbContainerList:大批次列表
            StringBuilder sbContainerList = new StringBuilder();
            sbContainerList.Append("(");
            foreach (DataRow dr in dtMain.Rows)
            {
                sbContainerList.AppendFormat("(cci.togetherflag = '{0}' AND cci.workflowstepid = '{1}') OR ", dr["togetherflag"], dr["workflowstepid"]);
            }
            sbContainerList.Remove(sbContainerList.ToString().Length - 3, 3);
            sbContainerList.Append(")");

            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.togetherflag,cci.containername,ws.workflowstepname,ws.workflowstepid,ws.sequence,cci.workflowstepsequence,");
            sb.AppendLine("       cci.workflowstepid currentworkflowstepid,cci.workflowstepname currentworkflowstepname,");
            sb.AppendLine("       CASE WHEN csd.plannedfinishdate IS NOT NULL THEN to_date(to_char(csd.plannedfinishdate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ");
            sb.AppendLine("            ELSE csd.plannedfinishdate END plannedfinishdate,");
            sb.AppendLine("       NVL(e2.fullname,e.fullname) fullname,DECODE(csr.usecompleted,1,csr.checkdate,NULL) checkdate,");
            sb.AppendLine("       DECODE(fp.productname,NULL,'非重点件号','重点件号') producttype,cci.plannedcompletiondate,cci.fromworkcentername,cci.containertype");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");//批次当前工艺路线上所有工序
            sb.AppendLine("LEFT JOIN containerstepdispatch cst ON cst.containerid = cci.containerid AND cst.workflowstepid = ws.workflowstepid");
            sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid");
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = cst.workerid");
            sb.AppendLine("LEFT JOIN containerstepdispatchdetail cspd ON cspd.containerstepdispatchid = cst.containerstepdispatchid");
            sb.AppendLine("LEFT JOIN employee e2 ON e2.employeeid = cspd.workerid");
            sb.AppendLine("LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE {0}", sbContainerList.ToString());
            //sb.AppendLine("ORDER BY cci.containername,ws.sequence");

            DataTable dtTemp = dal.GetDataTable(sb.ToString());

            //查询自动转工工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE s.factoryid <> cci.originalfactoryid AND {0}", sbContainerList.ToString());
            DataTable dtAutoSynergicSpecInfo = dal.GetDataTable(sb.ToString());

            //查询固定外委工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,cm.indate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE s.controlcode = 'LY05' AND {0}", sbContainerList.ToString());
            DataTable dtAutoCommissioning = dal.GetDataTable(sb.ToString());

            //查询手动转工工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containershiprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE csr.containershiprecordid IS NOT NULL AND s.factoryid = cci.originalfactoryid AND {0}", sbContainerList.ToString());
            DataTable dtSynergicInfo = dal.GetDataTable(sb.ToString());

            DataTable dtResult = dtTemp.DefaultView.ToTable(true, "plannedcompletiondate", "fromworkcentername", "togetherflag", "containername", "workflowstepname",
                                                                   "workflowstepid", "sequence","currentworkflowstepname","currentworkflowstepid","workflowstepsequence",
                                                                   "producttype", "plannedfinishdate", "containertype");
            dtResult.Columns.Add("checkdate");//工序完工日期

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("containername = '{0}' and workflowstepname = '{1}'", dr["containername"], dr["workflowstepname"]);

                DataRow[] drAutoSynergic = dtAutoSynergicSpecInfo.Select(filter);
                DataRow[] drAutoCommissioning = dtAutoCommissioning.Select(filter);
                DataRow[] drSynergicInfo = dtSynergicInfo.Select(filter);

                //同批次同工序完成日期取最大值
                dr["checkdate"] = dtTemp.Compute("Max(checkdate)", filter);

                //如果工序是外委序,将外委调度时间置为该工序检验时间
                if (drAutoSynergic.Length > 0)
                {
                    dr["checkdate"] = drAutoSynergic[0]["recivedate"];
                }
                //如果工序为自动转工序,将转入时间置为该工序检验时间
                if (drAutoCommissioning.Length > 0)
                {
                    dr["checkdate"] = drAutoCommissioning[0]["indate"];
                }
                //如果工序为临时转工序,将转入时间置为该工序检验时间
                if (drSynergicInfo.Length > 0)
                {
                    dr["checkdate"] = drSynergicInfo[0]["recivedate"];
                }
            }

            //根据大批次号、当前工序合并数据并处理
            DataTable dtResultCompute = dtResult.DefaultView.ToTable(true, "plannedcompletiondate", "fromworkcentername", "togetherflag", "workflowstepname", "workflowstepid",
                                                                            "sequence", "currentworkflowstepname", "currentworkflowstepid", "workflowstepsequence", "producttype", "containertype");
            dtResultCompute.Columns.Add("fullname");//报工人
            dtResultCompute.Columns.Add("checkdate");//完工日期
            dtResultCompute.Columns.Add("status");//状态
            dtResultCompute.Columns.Add("plannedfinishdate");//要求完成时间

            foreach (DataRow datarow in dtResultCompute.Rows)
            {
                string filter = string.Format("togetherflag = '{0}' and workflowstepid = '{1}' and currentworkflowstepid = '{2}'", 
                                              datarow["togetherflag"], datarow["workflowstepid"], datarow["currentworkflowstepid"]);

                DataRow[] drEmployee = dtTemp.Select(filter);
                StringBuilder fullname = new StringBuilder();

                //同批次同工序报工人整合为一条记录
                foreach (DataRow row in drEmployee)
                {
                    if (!fullname.ToString().Contains(row["fullname"].ToString()))
                    {
                        fullname.Append(row["fullname"].ToString() + "、");
                    }
                }
                if (fullname.ToString().Contains("、"))
                {
                    fullname.Remove(fullname.Length - 1, 1);
                }
                datarow["fullname"] = fullname.ToString();
                datarow["checkdate"] = dtResult.Compute("Max(checkdate)", filter);

                string finishDataHasNullfilter = string.Format(" plannedfinishdate is null and togetherflag = '{0}' and workflowstepid = '{1}' and currentworkflowstepid = '{2}'",
                                                               datarow["togetherflag"], datarow["workflowstepid"], datarow["currentworkflowstepid"]);

                DataRow[] drFinishDataHasNull = dtResult.Select(finishDataHasNullfilter);
                if (drFinishDataHasNull.Length > 0)
                {
                    datarow["plannedfinishdate"] = "";
                }
                else
                {
                    datarow["plannedfinishdate"] = dtResult.Compute("Max(plannedfinishdate)", filter);
                }

                //要求完成时间、检验时间都不为空
                if ((datarow["checkdate"].ToString() != "") && (datarow["plannedfinishdate"].ToString() != ""))
                {
                    DateTime checkdate = Convert.ToDateTime(datarow["checkdate"]);
                    DateTime plannedfinishdate = Convert.ToDateTime(datarow["plannedfinishdate"]);
                    //检验时间 > 要求完成时间，标记状态为"超期完成"
                    if (DateTime.Compare(checkdate, plannedfinishdate) > 0)
                    {
                        datarow["status"] = "超期完成";
                    }
                    else
                    {
                        //检验时间 <= 要求完成时间，标记状态为"按时完成"
                        datarow["status"] = "按时完成";
                    }
                }
                //要求完成时间不为空，检验时间为空
                if ((datarow["checkdate"].ToString() == "") && (datarow["plannedfinishdate"].ToString() != ""))
                {
                    DateTime now = DateTime.Now;
                    DateTime plannedfinishdate = Convert.ToDateTime(datarow["plannedfinishdate"]);
                    //如果当前时间>要求完成时间，标记状态为"超期未完成"
                    if (DateTime.Compare(now, plannedfinishdate) >= 0)
                    {
                        datarow["status"] = "超期未完成";
                    }
                    else
                    {
                        //如果当前时间 <= 要求完成时间，标记状态为"未检验"
                        datarow["status"] = "未检验";
                    }
                }
                //要求完成时间为空,则工序没有做排产操作，此时标记状态'工序为派工'
                if (datarow["plannedfinishdate"].ToString() == "")
                {
                    datarow["status"] = "工序未排产";
                }
            }

            DataTable dtContainer = dtResultCompute.DefaultView.ToTable(true, "fromworkcentername","togetherflag","currentworkflowstepname", "workflowstepsequence");
            dtContainer.Columns.Add("maxSequence", typeof(Int32)); // 最大工序
            dtContainer.Columns.Add("startSequence", typeof(Int32));// 起始序
            dtContainer.Columns.Add("endSequence", typeof(Int32));// 结束工序

            // 计算批次的工序总数,计算每个批次显示的起始工序号,结束工序号
            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" togetherflag = '{0}' and currentworkflowstepname = '{1}'", dr["togetherflag"], dr["currentworkflowstepname"]);
                int maxSequence = Convert.ToInt32(dtResult.Compute("Max(sequence)", filter).ToString());
                dr["maxSequence"] = Convert.ToInt32(maxSequence);
                int currentSeq = Convert.ToInt32(dr["workflowstepsequence"].ToString());
                int startSeq = 0, endSeq = 0;

                // 批次工序总数小于7序,显示所有工序
                if (maxSequence <= 7)
                {
                    startSeq = 1;
                    endSeq = 7;
                    dr["startSequence"] = startSeq;
                    dr["endSequence"] = endSeq;
                }
                else
                {
                    // 批次工序总数大于7,且当前序不大于第4序时,显示1--7序
                    if (currentSeq <= 3)
                    {
                        startSeq = 1;
                        endSeq = 7;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) <= maxSequence))
                    {
                        startSeq = currentSeq - 2;
                        endSeq = 4 + currentSeq;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) > maxSequence))
                    {
                        startSeq = maxSequence - 6;
                        endSeq = maxSequence;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                }

            }

            DataTable dtFocusProcess = new DataTable();
            dtFocusProcess.Columns.Add("PLANNEDCOMPLETIONDATE",typeof(DateTime));//批次计划完成日期
            dtFocusProcess.Columns.Add("FROMWORKCENTERNAME");
            dtFocusProcess.Columns.Add("CONTAINERNAME");
            dtFocusProcess.Columns.Add("WORKFLOWSTEPNAME");
            dtFocusProcess.Columns.Add("SEQUENCE");
            dtFocusProcess.Columns.Add("WORKFLOWSTEPSEQUENCE");
            dtFocusProcess.Columns.Add("PLANNEDFINISHDATE", typeof(DateTime));//工序要求完成日期
            dtFocusProcess.Columns.Add("PRODUCTTYPE");
            dtFocusProcess.Columns.Add("fullname");
            dtFocusProcess.Columns.Add("checkdate");
            dtFocusProcess.Columns.Add("status");
            dtFocusProcess.Columns.Add("containertype");

            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" togetherflag = '{0}' and sequence >= {1} and sequence <= {2} and currentworkflowstepname = '{3}'",
                                                dr["togetherflag"], dr["startSequence"], dr["endSequence"], dr["currentworkflowstepname"]);

                DataRow[] drContainerSpec = dtResultCompute.Select(filter);
                for (int i = 0; i < drContainerSpec.Length; i++)
                {
                    DataRow datarow = dtFocusProcess.NewRow();
                    datarow["PLANNEDCOMPLETIONDATE"] = drContainerSpec[i]["plannedcompletiondate"];
                    datarow["FROMWORKCENTERNAME"] = drContainerSpec[i]["fromworkcentername"];
                    datarow["CONTAINERNAME"] = drContainerSpec[i]["togetherflag"];
                    datarow["WORKFLOWSTEPNAME"] = drContainerSpec[i]["workflowstepname"];
                    datarow["SEQUENCE"] = drContainerSpec[i]["sequence"];
                    datarow["WORKFLOWSTEPSEQUENCE"] = drContainerSpec[i]["workflowstepsequence"];
                    if (drContainerSpec[i]["plannedfinishdate"].ToString() != "")
                    {
                        datarow["PLANNEDFINISHDATE"] = Convert.ToDateTime(drContainerSpec[i]["plannedfinishdate"]);
                    }
                    else
                    {
                        datarow["PLANNEDFINISHDATE"] = DBNull.Value;
                    }
                    datarow["PRODUCTTYPE"] = drContainerSpec[i]["producttype"];
                    datarow["fullname"] = drContainerSpec[i]["fullname"];
                    datarow["checkdate"] = drContainerSpec[i]["checkdate"];
                    datarow["status"] = drContainerSpec[i]["status"];
                    datarow["containertype"] = drContainerSpec[i]["containertype"];
                    dtFocusProcess.Rows.Add(datarow);
                }

                // 每个批次显示7道序，当工序数不够7时，添加空行凑够7道序
                if (drContainerSpec.Length < 7)
                {
                    for (int i = 0; i < (7 - drContainerSpec.Length); i++)
                    {
                        DataRow datarow = dtFocusProcess.NewRow();
                        datarow["FROMWORKCENTERNAME"] = dr["FROMWORKCENTERNAME"];
                        datarow["CONTAINERNAME"] = dr["togetherflag"];
                        dtFocusProcess.Rows.Add(datarow);
                    }
                }
            }

            foreach (DataRow dr in dtFocusProcess.Rows)
            {
                // 工序名称太长时，对超出长度的部分显示 "..."
                if (dr["WORKFLOWSTEPNAME"].ToString().Length > 13)
                {
                    dr["WORKFLOWSTEPNAME"] = dr["WORKFLOWSTEPNAME"].ToString().Substring(0, 10) + "...";
                }
                if (dr["fullname"].ToString().Length > 13)
                {
                    dr["fullname"] = dr["fullname"].ToString().Substring(0, 10) + "...";
                }
                if ((dr["WORKFLOWSTEPSEQUENCE"].ToString() == dr["SEQUENCE"].ToString()) && dr["WORKFLOWSTEPSEQUENCE"].ToString() != "")
                {
                    dr["WORKFLOWSTEPNAME"] = "<i class='layui-icon layui-icon-flag'></i> " + dr["WORKFLOWSTEPNAME"];
                }
            }

            containerCount = dtFocusProcess.Rows.Count / 7;

            return dtFocusProcess;
        }

        //工序排产完成情况看板（单件单卡根据当前序进行合并） add zhunan 2019.1.17
        public DataTable GetContainerStepPlanFinishInfoNew(Dictionary<string, object> para, out int pageCount, out int containerCount, out int rowCount)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.*");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("      SELECT cci.togetherflag,cci.workflowstepname currentworkflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype,MAX(ws.sequence) maxSequence");
            sb.AppendLine("      FROM containercurrentinfo cci");
            sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
            sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
            sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype = 7 ");
            #region 查询条件
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryname = '{0}'", para["factoryname"].ToString());
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND cci.fromworkcentername = '{0}'", para["workcentername"].ToString());
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                }
            }
            if (para.ContainsKey("isFocusProductname"))
            {

                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sb.AppendLine(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sb.AppendLine(" AND fp.productname IS NULL ");
                }
            }
            if (para.ContainsKey("containername"))
            {
                if (para["containername"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                }
            }
            #endregion
            sb.AppendLine("      GROUP BY cci.togetherflag,cci.workflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype");
            sb.AppendLine("");
            sb.AppendLine("      UNION ALL");
            sb.AppendLine("");
            sb.AppendLine("      SELECT cci.containername togetherflag,cci.workflowstepname currentworkflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype,MAX(ws.sequence) maxSequence");
            sb.AppendLine("      FROM containercurrentinfo cci");
            sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
            sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
            sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
            sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype <> 7 ");
            #region 查询条件
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryname = '{0}'", para["factoryname"].ToString());
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND cci.fromworkcentername = '{0}'", para["workcentername"].ToString());
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                }
            }
            if (para.ContainsKey("isFocusProductname"))
            {

                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sb.AppendLine(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sb.AppendLine(" AND fp.productname IS NULL ");
                }
            }
            if (para.ContainsKey("containername"))
            {
                if (para["containername"].ToString() != "")
                {
                    sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                }
            }
            #endregion
            sb.AppendLine("      GROUP BY cci.containername,cci.workflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype");
            sb.AppendLine(") t");
            sb.AppendLine("ORDER BY t.togetherflag,t.workflowstepsequence");

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sb.ToString(), Convert.ToInt32(para["currentPage"]), Convert.ToInt32(para["pageSize"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            containerCount = 0;

            if (retR.DBTable.Rows.Count == 0)
            {
                return new DataTable();
            }

            DataTable dtMain = retR.DBTable;

            //sbContainerList:大批次列表
            StringBuilder sbContainerList = new StringBuilder();
            sbContainerList.Append("(");
            foreach (DataRow dr in dtMain.Rows)
            {
                sbContainerList.AppendFormat("(cci.togetherflag = '{0}' AND cci.workflowstepname = '{1}') OR ", dr["togetherflag"], dr["currentworkflowstepname"]);
            }
            sbContainerList.Remove(sbContainerList.ToString().Length - 3, 3);
            sbContainerList.Append(")");

            //add zhunan 2019.2.21
            string ContainerList = sbContainerList.ToString();
            ContainerList = ContainerList.Replace("togetherflag", "containername");

            sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT cci.togetherflag,cci.containername,ws.workflowstepname,ws.workflowstepid,ws.sequence,cci.workflowstepsequence,");
            sb.AppendLine("       cci.workflowstepid currentworkflowstepid,cci.workflowstepname currentworkflowstepname,");
            sb.AppendLine("       CASE WHEN csd.plannedfinishdate IS NOT NULL THEN to_date(to_char(csd.plannedfinishdate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ");
            sb.AppendLine("            ELSE csd.plannedfinishdate END plannedfinishdate,");
            sb.AppendLine("       csr.reportemployeename fullname,DECODE(csr.usecompleted,1,csr.checkdate,NULL) checkdate,");
            sb.AppendLine("       DECODE(fp.productname,NULL,'非重点件号','重点件号') producttype,cci.plannedcompletiondate,cci.fromworkcentername,cci.containertype");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");//批次当前工艺路线上所有工序
            sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid");
            sb.AppendLine("LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE cci.containertype=7 AND ws.sequence IS NOT NULL AND {0}", sbContainerList.ToString());

            sb.AppendLine("UNION ALL");
                
            sb.AppendLine("SELECT DISTINCT cci.togetherflag,cci.containername,ws.workflowstepname,ws.workflowstepid,ws.sequence,cci.workflowstepsequence,");
            sb.AppendLine("       cci.workflowstepid currentworkflowstepid,cci.workflowstepname currentworkflowstepname,");
            sb.AppendLine("       CASE WHEN csd.plannedfinishdate IS NOT NULL THEN to_date(to_char(csd.plannedfinishdate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ");
            sb.AppendLine("            ELSE csd.plannedfinishdate END plannedfinishdate,");
            sb.AppendLine("       csr.reportemployeename fullname,DECODE(csr.usecompleted,1,csr.checkdate,NULL) checkdate,");
            sb.AppendLine("       DECODE(fp.productname,NULL,'非重点件号','重点件号') producttype,cci.plannedcompletiondate,cci.fromworkcentername,cci.containertype");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");//批次当前工艺路线上所有工序
            sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid");
            sb.AppendLine("LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE cci.containertype <> 7 AND ws.sequence IS NOT NULL AND {0}", ContainerList);

            DataTable dtContainerAllSpec = dal.GetDataTable(sb.ToString());

            DataTable dtContainer = dtMain.Copy();
            dtContainer.Columns.Add("startSequence");
            dtContainer.Columns.Add("endSequence");
            #region 计算批次的工序总数,计算每个批次显示的起始工序号,结束工序号
            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" togetherflag = '{0}' and currentworkflowstepname = '{1}'", dr["togetherflag"], dr["currentworkflowstepname"]);
                int maxSequence = Convert.ToInt32(dr["maxSequence"]);
                int currentSeq = Convert.ToInt32(dr["workflowstepsequence"].ToString());
                int startSeq = 0, endSeq = 0;

                // 批次工序总数小于7序,显示所有工序
                if (maxSequence <= 7)
                {
                    startSeq = 1;
                    endSeq = 7;
                    dr["startSequence"] = startSeq;
                    dr["endSequence"] = endSeq;
                }
                else
                {
                    // 批次工序总数大于7,且当前序不大于第4序时,显示1--7序
                    if (currentSeq <= 3)
                    {
                        startSeq = 1;
                        endSeq = 7;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) <= maxSequence))
                    {
                        startSeq = currentSeq - 2;
                        endSeq = 4 + currentSeq;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                    if ((currentSeq > 3) && ((4 + currentSeq) > maxSequence))
                    {
                        startSeq = maxSequence - 6;
                        endSeq = maxSequence;
                        dr["startSequence"] = startSeq;
                        dr["endSequence"] = endSeq;
                    }
                }
            }
            #endregion

            DataTable dtTemp = dtContainerAllSpec.Clone();
            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter;
                if (dr["containertype"].ToString() != "2")
                {
                    filter = string.Format("togetherflag = '{0}' and currentworkflowstepname = '{1}' and sequence >= '{2}' and sequence <= '{3}' ",
                                            dr["togetherflag"], dr["currentworkflowstepname"], dr["startSequence"], dr["endSequence"]
                                          );
                }
                else
                {
                    filter = string.Format("containername = '{0}' and currentworkflowstepname = '{1}' and sequence >= '{2}' and sequence <= '{3}' ",
                                            dr["togetherflag"], dr["currentworkflowstepname"], dr["startSequence"], dr["endSequence"]
                                          );
                }
                dtTemp.Merge(dtContainerAllSpec.Select(filter).CopyToDataTable());
            }

            //查询自动转工工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT t.containername,t.workflowstepname,t.recivedate");
            sb.AppendLine("FROM (");
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containershiprecord csd");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid");
            sb.AppendLine("LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid");
            sb.AppendLine("LEFT JOIN containershiprecordstep csrr ON csrr.containershiprecordid = csr.containershiprecordid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowstepid = csrr.workflowstepid");
            sb.AppendFormat("WHERE csr.recivedate IS NOT NULL AND {0}", ContainerList);

            sb.AppendLine("UNION ALL");

            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,csr.recivedate");
            sb.AppendLine("FROM containershiprecord csd");
            sb.AppendLine("LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid");
            sb.AppendLine("LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid");
            sb.AppendLine("LEFT JOIN containershiprecordstep csrr ON csrr.containershiprecordid = csr.containershiprecordid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowstepid = csrr.workflowstepid");
            sb.AppendFormat("WHERE csr.recivedate IS NOT NULL AND {0}", sbContainerList.ToString());
            sb.AppendLine(") t");

            DataTable dtAutoSynergicSpecInfo = dal.GetDataTable(sb.ToString());

            //查询固定外委工序转入时间
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,cm.indate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE cci.containertype <> 7 AND s.controlcode = 'LY05' AND {0}", ContainerList);

            sb.AppendLine("UNION ALL ");

            sb.AppendLine("SELECT cci.containername,ws.workflowstepname,cm.indate");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowid = cci.workflowid");
            sb.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)");
            sb.AppendLine("LEFT JOIN containercommissioning cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid");
            sb.AppendFormat("WHERE cci.containertype = 7 AND s.controlcode = 'LY05' AND {0}", sbContainerList.ToString());

            DataTable dtAutoCommissioning = dal.GetDataTable(sb.ToString());

            DataTable dtResult = dtTemp.DefaultView.ToTable(true, "plannedcompletiondate", "fromworkcentername", "togetherflag", "containername", "workflowstepname",
                                                                   "workflowstepid", "sequence", "currentworkflowstepname", "workflowstepsequence",
                                                                   "producttype", "plannedfinishdate", "containertype");
            dtResult.Columns.Add("checkdate");//工序完工日期

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("containername = '{0}' and workflowstepname = '{1}'", dr["containername"], dr["workflowstepname"]);

                DataRow[] drAutoSynergic = dtAutoSynergicSpecInfo.Select(filter);
                DataRow[] drAutoCommissioning = dtAutoCommissioning.Select(filter);
                //DataRow[] drSynergicInfo = dtSynergicInfo.Select(filter);

                //同批次同工序完成日期取最大值
                dr["checkdate"] = dtTemp.Compute("Max(checkdate)", filter);

                //如果工序是外委序,将外委调度时间置为该工序检验时间
                if (drAutoSynergic.Length > 0)
                {
                    dr["checkdate"] = drAutoSynergic[0]["recivedate"];
                }
                //如果工序为自动转工序,将转入时间置为该工序检验时间
                if (drAutoCommissioning.Length > 0)
                {
                    dr["checkdate"] = drAutoCommissioning[0]["indate"];
                }
            }

            //根据大批次号、当前工序合并数据并处理
            foreach (DataRow dr in dtResult.Rows)
            {
                if (dr["containertype"].ToString() != "7")
                {
                    dr["togetherflag"] = dr["containername"];
                }
            }

            DataTable dtResultCompute = dtResult.DefaultView.ToTable(true, "fromworkcentername", "togetherflag", "workflowstepname",
                                                                            "sequence", "currentworkflowstepname","workflowstepsequence", "producttype", "containertype");
            dtResultCompute.Columns.Add("fullname");//报工人
            dtResultCompute.Columns.Add("checkdate");//完工日期
            dtResultCompute.Columns.Add("status");//状态
            dtResultCompute.Columns.Add("plannedfinishdate");//要求完成时间
            dtResultCompute.Columns.Add("plannedcompletiondate");//批次最大要求完成时间

            foreach (DataRow datarow in dtResultCompute.Rows)
            {
                string filter;
                if (datarow["containertype"].ToString() != "2")
                {
                    filter = string.Format("togetherflag = '{0}' and workflowstepname = '{1}' and currentworkflowstepname = '{2}'",
                                            datarow["togetherflag"], datarow["workflowstepname"], datarow["currentworkflowstepname"]);
                }
                else
                {
                    filter = string.Format("containername = '{0}' and workflowstepname = '{1}' and currentworkflowstepname = '{2}'",
                                            datarow["togetherflag"], datarow["workflowstepname"], datarow["currentworkflowstepname"]);
                }

                //批次最大要求完成日期
                datarow["plannedcompletiondate"] = dtResult.Compute("Max(plannedcompletiondate)", filter);

                DataRow[] drEmployee = dtTemp.Select(filter);
                StringBuilder fullname = new StringBuilder();

                //同批次同工序报工人整合为一条记录
                foreach (DataRow row in drEmployee)
                {
                    if (!fullname.ToString().Contains(row["fullname"].ToString()))
                    {
                        fullname.Append(row["fullname"].ToString() + "、");
                    }
                }
                if (fullname.ToString().Contains("、"))
                {
                    fullname.Remove(fullname.Length - 1, 1);
                }
                datarow["fullname"] = fullname.ToString();
                datarow["checkdate"] = dtResult.Compute("Max(checkdate)", filter);

                string finishDataHasNullfilter;
                if (datarow["containertype"].ToString() != "2")
                {
                    finishDataHasNullfilter = string.Format(" plannedfinishdate is null and togetherflag = '{0}' and workflowstepname = '{1}' and currentworkflowstepname = '{2}'",
                                                               datarow["togetherflag"], datarow["workflowstepname"], datarow["currentworkflowstepname"]);
                }
                else
                {
                    finishDataHasNullfilter = string.Format(" plannedfinishdate is null and containername = '{0}' and workflowstepname = '{1}' and currentworkflowstepname = '{2}'",
                                                               datarow["togetherflag"], datarow["workflowstepname"], datarow["currentworkflowstepname"]);
                }

                DataRow[] drFinishDataHasNull = dtResult.Select(finishDataHasNullfilter);
                if (drFinishDataHasNull.Length > 0)
                {
                    datarow["plannedfinishdate"] = "";
                }
                else
                {
                    datarow["plannedfinishdate"] = dtResult.Compute("Max(plannedfinishdate)", filter);
                }

                //要求完成时间、检验时间都不为空
                if ((datarow["checkdate"].ToString() != "") && (datarow["plannedfinishdate"].ToString() != ""))
                {
                    DateTime checkdate = Convert.ToDateTime(datarow["checkdate"]);
                    DateTime plannedfinishdate = Convert.ToDateTime(datarow["plannedfinishdate"]);
                    //检验时间 > 要求完成时间，标记状态为"超期完成"
                    if (DateTime.Compare(checkdate, plannedfinishdate) > 0)
                    {
                        datarow["status"] = "超期完成";
                    }
                    else
                    {
                        //检验时间 <= 要求完成时间，标记状态为"按时完成"
                        datarow["status"] = "按时完成";
                    }
                }
                //要求完成时间不为空，检验时间为空
                if ((datarow["checkdate"].ToString() == "") && (datarow["plannedfinishdate"].ToString() != ""))
                {
                    DateTime now = DateTime.Now;
                    DateTime plannedfinishdate = Convert.ToDateTime(datarow["plannedfinishdate"]);
                    //如果当前时间>要求完成时间，标记状态为"超期未完成"
                    if (DateTime.Compare(now, plannedfinishdate) >= 0)
                    {
                        datarow["status"] = "超期未完成";
                    }
                    else
                    {
                        //如果当前时间 <= 要求完成时间，标记状态为"未检验"
                        datarow["status"] = "未检验";
                    }
                }
                //要求完成时间为空,则工序没有做排产操作，此时标记状态'工序为派工'
                if (datarow["plannedfinishdate"].ToString() == "")
                {
                    datarow["status"] = "工序未排产";
                }
            }

            DataTable dtFocusProcess = new DataTable();
            dtFocusProcess.Columns.Add("PLANNEDCOMPLETIONDATE", typeof(DateTime));//批次计划完成日期
            dtFocusProcess.Columns.Add("FROMWORKCENTERNAME");
            dtFocusProcess.Columns.Add("CONTAINERNAME");
            dtFocusProcess.Columns.Add("WORKFLOWSTEPNAME");
            dtFocusProcess.Columns.Add("SEQUENCE");
            dtFocusProcess.Columns.Add("WORKFLOWSTEPSEQUENCE");
            dtFocusProcess.Columns.Add("PLANNEDFINISHDATE", typeof(DateTime));//工序要求完成日期
            dtFocusProcess.Columns.Add("PRODUCTTYPE");
            dtFocusProcess.Columns.Add("fullname");
            dtFocusProcess.Columns.Add("checkdate");
            dtFocusProcess.Columns.Add("status");
            dtFocusProcess.Columns.Add("containertype");

            foreach (DataRow dr in dtContainer.Rows)
            {
                string filter = string.Format(" togetherflag = '{0}' and sequence >= {1} and sequence <= {2} and currentworkflowstepname = '{3}'",
                                                dr["togetherflag"], dr["startSequence"], dr["endSequence"], dr["currentworkflowstepname"]);

                DataRow[] drContainerSpec = dtResultCompute.Select(filter);
                for (int i = 0; i < drContainerSpec.Length; i++)
                {
                    DataRow datarow = dtFocusProcess.NewRow();
                    datarow["PLANNEDCOMPLETIONDATE"] = drContainerSpec[i]["plannedcompletiondate"];
                    datarow["FROMWORKCENTERNAME"] = drContainerSpec[i]["fromworkcentername"];
                    datarow["CONTAINERNAME"] = drContainerSpec[i]["togetherflag"];
                    datarow["WORKFLOWSTEPNAME"] = drContainerSpec[i]["workflowstepname"];
                    datarow["SEQUENCE"] = drContainerSpec[i]["sequence"];
                    datarow["WORKFLOWSTEPSEQUENCE"] = drContainerSpec[i]["workflowstepsequence"];
                    if (drContainerSpec[i]["plannedfinishdate"].ToString() != "")
                    {
                        datarow["PLANNEDFINISHDATE"] = Convert.ToDateTime(drContainerSpec[i]["plannedfinishdate"]);
                    }
                    else
                    {
                        datarow["PLANNEDFINISHDATE"] = DBNull.Value;
                    }
                    datarow["PRODUCTTYPE"] = drContainerSpec[i]["producttype"];
                    datarow["fullname"] = drContainerSpec[i]["fullname"];
                    datarow["checkdate"] = drContainerSpec[i]["checkdate"];
                    datarow["status"] = drContainerSpec[i]["status"];
                    datarow["containertype"] = drContainerSpec[i]["containertype"];
                    dtFocusProcess.Rows.Add(datarow);
                }

                // 每个批次显示7道序，当工序数不够7时，添加空行凑够7道序
                if (drContainerSpec.Length < 7)
                {
                    for (int i = 0; i < (7 - drContainerSpec.Length); i++)
                    {
                        DataRow datarow = dtFocusProcess.NewRow();
                        datarow["FROMWORKCENTERNAME"] = dr["FROMWORKCENTERNAME"];
                        datarow["CONTAINERNAME"] = dr["togetherflag"];
                        dtFocusProcess.Rows.Add(datarow);
                    }
                }
            }

            foreach (DataRow dr in dtFocusProcess.Rows)
            {
                // 工序名称太长时，对超出长度的部分显示 "..."
                if (dr["WORKFLOWSTEPNAME"].ToString().Length > 13)
                {
                    dr["WORKFLOWSTEPNAME"] = dr["WORKFLOWSTEPNAME"].ToString().Substring(0, 10) + "...";
                }
                if (dr["fullname"].ToString().Length > 13)
                {
                    dr["fullname"] = dr["fullname"].ToString().Substring(0, 10) + "...";
                }
                if ((dr["WORKFLOWSTEPSEQUENCE"].ToString() == dr["SEQUENCE"].ToString()) && dr["WORKFLOWSTEPSEQUENCE"].ToString() != "")
                {
                    dr["WORKFLOWSTEPNAME"] = "<i class='layui-icon layui-icon-flag'></i> " + dr["WORKFLOWSTEPNAME"];
                }
            }

            containerCount = dtFocusProcess.Rows.Count / 7;

            return dtFocusProcess;
        }

        //查询批次总条数 add zhunan 2020.6.30
        public int GetTotal(Dictionary<string, object> para)
        {
            if (para["isMerge"].ToString() == "是")
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT t.*");
                sb.AppendLine("FROM");
                sb.AppendLine("(");
                sb.AppendLine("      SELECT cci.togetherflag,cci.workflowstepname currentworkflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype,MAX(ws.sequence) maxSequence");
                sb.AppendLine("      FROM containercurrentinfo cci");
                sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
                sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
                sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
                sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid");
                sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
                sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype = 7 ");
                #region 查询条件
                if (para.ContainsKey("factoryid"))
                {
                    if (para["factoryid"].ToString() != "")
                    {
                        sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                    }
                }
                if (para.ContainsKey("workcenterid"))
                {
                    if (para["workcenterid"].ToString() != "全部")
                    {
                        sb.AppendFormat(" AND cci.fromworkcenterid = '{0}'", para["workcenterid"].ToString());
                    }
                }
                if (para.ContainsKey("productname"))
                {
                    if (para["productname"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                    }
                }
                if (para.ContainsKey("isFocusProductname"))
                {

                    if (para["isFocusProductname"].ToString() == "重点件号")
                    {
                        sb.AppendLine(" AND fp.productname IS NOT NULL ");
                    }
                    if (para["isFocusProductname"].ToString() == "非重点件")
                    {
                        sb.AppendLine(" AND fp.productname IS NULL ");
                    }
                }
                if (para.ContainsKey("containername"))
                {
                    if (para["containername"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                    }
                }
                //是否到料
                if (para.ContainsKey("isDL"))
                {

                    if (para["isDL"].ToString() == "是")
                    {
                        sb.AppendLine(" AND cci.materialdate IS NOT NULL ");
                    }
                    else
                    {
                        sb.AppendLine(" AND cci.materialdate IS NULL ");
                    }
                }
                #endregion
                sb.AppendLine("      GROUP BY cci.togetherflag,cci.workflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype");
                sb.AppendLine("");
                sb.AppendLine("      UNION ALL");
                sb.AppendLine("");
                sb.AppendLine("      SELECT cci.containername togetherflag,cci.workflowstepname currentworkflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype,MAX(ws.sequence) maxSequence");
                sb.AppendLine("      FROM containercurrentinfo cci");
                sb.AppendLine("      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
                sb.AppendLine("      LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
                sb.AppendLine("      LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
                sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid");
                sb.AppendLine("      WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL ");
                sb.AppendLine("            AND cci.workflowroute IS NULL AND cci.containertype <> 7 ");
                #region 查询条件
                if (para.ContainsKey("factoryid"))
                {
                    if (para["factoryid"].ToString() != "")
                    {
                        sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                    }
                }
                if (para.ContainsKey("workcenterid"))
                {
                    if (para["workcenterid"].ToString() != "全部")
                    {
                        sb.AppendFormat(" AND cci.fromworkcenterid = '{0}'", para["workcenterid"].ToString());
                    }
                }
                if (para.ContainsKey("productname"))
                {
                    if (para["productname"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                    }
                }
                if (para.ContainsKey("isFocusProductname"))
                {

                    if (para["isFocusProductname"].ToString() == "重点件号")
                    {
                        sb.AppendLine(" AND fp.productname IS NOT NULL ");
                    }
                    if (para["isFocusProductname"].ToString() == "非重点件")
                    {
                        sb.AppendLine(" AND fp.productname IS NULL ");
                    }
                }
                if (para.ContainsKey("containername"))
                {
                    if (para["containername"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                    }
                }
                //是否到料
                if (para.ContainsKey("isDL"))
                {

                    if (para["isDL"].ToString() == "是")
                    {
                        sb.AppendLine(" AND cci.materialdate IS NOT NULL ");
                    }
                    else
                    {
                        sb.AppendLine(" AND cci.materialdate IS NULL ");
                    }
                }
                #endregion
                sb.AppendLine("      GROUP BY cci.containername,cci.workflowstepname,cci.workflowstepsequence,cci.fromworkcentername,cci.containertype");
                sb.AppendLine(") t");
                sb.AppendLine("ORDER BY t.togetherflag,t.workflowstepsequence");

                return dal.GetDataTable(sb.ToString()).Rows.Count;
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("SELECT DISTINCT cci.containername");
                sb.AppendLine("FROM containercurrentinfo cci");
                sb.AppendLine("LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid");
                sb.AppendLine("LEFT JOIN focusproduct fp ON cci.productname = fp.productname");
                sb.AppendLine("LEFT JOIN containerstepplan csd ON csd.containerid = cci.containerid");
                sb.AppendLine("WHERE cci.isinstore = 0 AND csd.containerid IS NOT NULL AND cci.containerstatus <> 2 AND csd.plannedfinishdate IS NOT NULL AND cci.workflowroute IS NULL ");
                if (para.ContainsKey("factoryname"))
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"].ToString());
                }
                if (para.ContainsKey("factoryid"))
                {
                    if (para["factoryid"].ToString() != "")
                    {
                        sb.AppendFormat("            AND f.factoryid = '{0}'", para["factoryid"].ToString());
                    }
                }
                if (para.ContainsKey("workcenterid"))
                {
                    if (para["workcenterid"].ToString() != "全部")
                    {
                        sb.AppendFormat(" AND cci.fromworkcenterid = '{0}'", para["workcenterid"].ToString());
                    }
                }
                if (para.ContainsKey("productname"))
                {
                    if (para["productname"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.productname = '{0}'", para["productname"].ToString());
                    }
                }
                if (para.ContainsKey("isFocusProductname"))
                {

                    if (para["isFocusProductname"].ToString() == "重点件号")
                    {
                        sb.AppendLine(" AND fp.productname IS NOT NULL ");
                    }
                    if (para["isFocusProductname"].ToString() == "非重点件")
                    {
                        sb.AppendLine(" AND fp.productname IS NULL ");
                    }
                }
                if (para.ContainsKey("containername"))
                {
                    if (para["containername"].ToString() != "")
                    {
                        sb.AppendFormat(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
                    }
                }
                //是否到料
                if (para.ContainsKey("isDL"))
                {
                    if (para["isDL"].ToString() == "是")
                    {
                        sb.AppendLine(" AND cci.materialdate IS NOT NULL ");
                    }
                    else
                    {
                        sb.AppendLine(" AND cci.materialdate IS NULL ");
                    }
                }
                return dal.GetDataTable(sb.ToString()).Rows.Count;
            }
        
        }
    }
}
